** BH Lags for The Effects of Changes in Local Bank Health on Household Consumption
** by Daniel Cooper and Joe Peek, ReStat 2020
/* ////////////////////////////////////////////////////////////////////////// */
clear all
set more off
set maxvar 30000

/* This code sets up (lagged) BH values for each msa, state, and rural area (nmst).  
See the text for more information about the timing of the variables, but generally we 
want lags so we can look at the conditions of banks prior to a households' interview
Matching of BH data and PSID at MSA level must be done on the PSID terminal/enclave  */

local msa "MSA"
local nmst "nMSA"
local st "ST"

* update the line below with the relevant path
local data /shared/Joe_Peek/bankhealth_nocamels/data

#delimit ;

* load the relevant BH data that is compiled from the Call Report and SOD data;
* RUN THAT CODE FIRST;
  
foreach r in msa nmst st {;
use `data'/bh2018-06c-``r''-master-27feb2019_ren.dta, clear;

*generating year and qtr from dateq;
g year = year(dofq(dateq));
g qtr = quarter(dofq(dateq));

********************;
*For MSA level data;
********************;
if "`r'"=="msa" {;
rename msabr CBSA;
sort CBSA dateq;
drop if CBSA ==. ; 
  
foreach x of varlist _all {;
	by CBSA: gen l1_`x'_msa = `x'[_n-1];
	by CBSA: gen l2_`x'_msa = `x'[_n-2];
	by CBSA: gen l3_`x'_msa = `x'[_n-3];
	by CBSA: gen l4_`x'_msa = `x'[_n-4];
};

drop l1_CBSA_msa l2_CBSA_msa l3_CBSA_msa l4_CBSA_msa ;

keep year qtr CBSA l*;
sort year qtr CBSA;

save `data'/bnkh_msa_lags_fin_hmr_15.dta, replace;
};

*State and non-MSA state;
if "`r'"=="st" | "`r'"=="nmst" {;
*generating FIPS code from state name abbreviation;
g statefips = .;
replace statefips=1 if stalpbr=="AL";	//Alabama
replace statefips=2 if stalpbr=="AK";	//Alaska
replace statefips=4 if stalpbr=="AZ";	//Arizona
replace statefips=5 if stalpbr=="AR";	//Arkansas
replace statefips=6 if stalpbr=="CA";	//California
replace statefips=8 if stalpbr=="CO";	//Colorado
replace statefips=9 if stalpbr=="CT";	//Connecticut
replace statefips=10 if stalpbr=="DE"; //Delaware
replace statefips=11 if stalpbr=="DC"; //District of Columbia
replace statefips=12 if stalpbr=="FL"; //Florida
replace statefips=13 if stalpbr=="GA"; //Georgia
replace statefips=15 if stalpbr=="HI"; //Hawaii
replace statefips=16 if stalpbr=="ID"; //Idaho
replace statefips=17 if stalpbr=="IL"; //Illinois
replace statefips=18 if stalpbr=="IN"; //Indiana
replace statefips=19 if stalpbr=="IA"; //Iowa
replace statefips=20 if stalpbr=="KS"; //Kansas
replace statefips=21 if stalpbr=="KY"; //Kentucky
replace statefips=22 if stalpbr=="LA"; //Louisiana
replace statefips=23 if stalpbr=="ME"; //Maine
replace statefips=24 if stalpbr=="MD"; //Maryland
replace statefips=25 if stalpbr=="MA"; //Massachusetts
replace statefips=26 if stalpbr=="MI"; //Michigan
replace statefips=27 if stalpbr=="MN"; //Minnesota
replace statefips=28 if stalpbr=="MS"; //Mississippi
replace statefips=29 if stalpbr=="MO"; //Missouri
replace statefips=30 if stalpbr=="MT"; //Montana
replace statefips=31 if stalpbr=="NE"; //Nebraska
replace statefips=32 if stalpbr=="NV"; //Nevada
replace statefips=33 if stalpbr=="NH"; //New Hampshire
replace statefips=34 if stalpbr=="NJ"; //New Jersey
replace statefips=35 if stalpbr=="NM"; //New Mexico
replace statefips=36 if stalpbr=="NY"; //New York
replace statefips=37 if stalpbr=="NC"; //North Carolina
replace statefips=38 if stalpbr=="ND"; //North Dakota
replace statefips=39 if stalpbr=="OH"; //Ohio
replace statefips=40 if stalpbr=="OK"; //Oklahoma
replace statefips=41 if stalpbr=="OR"; //Oregon
replace statefips=42 if stalpbr=="PA"; //Pennsylvania
replace statefips=44 if stalpbr=="RI"; //Rhode Island
replace statefips=45 if stalpbr=="SC"; //South Carolina
replace statefips=46 if stalpbr=="SD"; //South Dakota
replace statefips=47 if stalpbr=="TN"; //Tennessee
replace statefips=48 if stalpbr=="TX"; //Texas
replace statefips=49 if stalpbr=="UT"; //Utah
replace statefips=50 if stalpbr=="VT"; //Vermont
replace statefips=51 if stalpbr=="VA"; //Virginia
replace statefips=53 if stalpbr=="WA"; //Washington
replace statefips=54 if stalpbr=="WV"; //West Virginia
replace statefips=55 if stalpbr=="WI"; //Wisconsin
replace statefips=56 if stalpbr=="WY"; //Wyoming

*psid crosswalk;
g state=statefips if statefips==1;
replace state=50 if statefips==2;
replace state=statefips-2 if statefips<=6 & statefips>2;
replace state=statefips-3 if statefips<15 & statefips>6;
replace state=51 if statefips==15;
replace state=statefips-5 if statefips<43 & statefips>15;
replace state=statefips-6 if statefips>43 & statefips<52;
replace state=statefips-7 if statefips>52 & statefips<=56;
rename statefips fipstate;

sort state year qtr;

*********************;
*For State level data;
*********************;
if "`r'"=="st" {;
foreach x of varlist _all {;
	by state: gen l1_`x'_st = `x'[_n-1];
	by state: gen l2_`x'_st = `x'[_n-2];
	by state: gen l3_`x'_st = `x'[_n-3];
	by state: gen l4_`x'_st = `x'[_n-4];
};

keep year qtr state stalpbr l*;
sort year qtr state;

save `data'/bankh_state_lags_fin_hmr_15.dta, replace;
};

******************************;
*For Non-MSA State level data;
******************************;
if "`r'"=="nmst" {;
foreach x of varlist _all {;
	by state: gen l1_`x'_nmst = `x'[_n-1];
	by state: gen l2_`x'_nmst = `x'[_n-2];
	by state: gen l3_`x'_nmst = `x'[_n-3];
	by state: gen l4_`x'_nmst = `x'[_n-4];
};

keep year qtr state l*;
sort year qtr state;

save `data'/bankh_nmst_lags_fin_hmr_15.dta, replace;
};
};
};
